package DBConnection;

import Bank.Account;
import Bank.Transaction;
import Bank.User;
import org.apache.log4j.Logger;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Locale;
import java.util.Objects;

public class DBAPI {

    private static final Logger log = Logger.getLogger(DBAPI.class);

    //entities
    public static final BigInteger USER = new BigInteger("9000001428080938856");
    public static final BigInteger ACCOUNT = new BigInteger("9000001428080958725");
    public static final BigInteger TRANSACTION = new BigInteger("9000001428080960200");
    //User
    public static final BigInteger ATTR_USER_NAME = new BigInteger("9000001428166313575");
    public static final BigInteger ATTR_PHONE = new BigInteger("9000001428166313576");
    public static final BigInteger ATTR_MAIL = new BigInteger("9000001429188446017");
    public static final BigInteger ATTR_LOGIN = new BigInteger("9000001430474099498");
    public static final BigInteger ATTR_PASSWORD = new BigInteger("9000001429971012173");
    //Account
    public static final BigInteger ATTR_USER_ID = new BigInteger("9000001428082196927");
    public static final BigInteger ATTR_AMOUNT = new BigInteger("9000001428082196964");
    public static final BigInteger ATTR_ACCOUNT_NUMBER = new BigInteger("9000001431344467496");
    //Transaction
    public static final BigInteger ATTR_FROM = new BigInteger("9000001428082196969");
    public static final BigInteger ATTR_TARGET = new BigInteger("9000001428082196972");
    public static final BigInteger ATTR_INITIATOR = new BigInteger("9000001428082196975");
    public static final BigInteger ATTR_COUNT = new BigInteger("9000001428082196980");
    public static final BigInteger ATTR_DATE = new BigInteger("9000001428082196984");
    public static final BigInteger ATTR_TRANSACTION_STATUS = new BigInteger("9000001431610796643");


    //returns DBObjects with id
    public static DBObject getObject(BigInteger id){

        ArrayList<Object> params = new ArrayList<>();
        params.add(id);
        return DBTools.executeSelect("select o.Object_id, O.Name, O.REFERENCE obj_ref, O.PARENT_ID, o.Object_type_id, a.ATTRIBUTE_ID, a.ATTR_TYPE, p.Value, p.DATA_VALUE, p.CLOB_VALUE, r.REFERENCE" +
                "  from Objects O " +
                "  left JOIN ATT_OBJ_TYPES aot on  O.OBJECT_TYPE_ID = aot.OBJECT_TYPE_ID" +
                "  LEFT join ATTRIBUTES a on aot.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join  Params p on p.OBJECT_ID = O.OBJECT_ID and p.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join REFERENCES r on r.OBJECT_ID = O.OBJECT_ID and r.ATTRIBUTE_ID = aot.ATTRIBUTE_ID" +
                "  where O.OBJECT_ID = ? ", params, new ResultSetHandler<DBObject>() {
            @Override
            public DBObject handle(ResultSet rs) {
                DBObject result;
                HashMap<BigInteger, Object> params = new HashMap<>();
                try {
                    rs.next();
                    String name = rs.getString("NAME");
                    BigInteger type = rs.getBigDecimal("Object_type_id").toBigInteger();
                    BigDecimal buf = rs.getBigDecimal("obj_ref");
                    BigInteger reference = new BigInteger("0");
                    if(buf != null)
                    {
                        reference = buf.toBigInteger();
                    }
                    BigInteger parent_id = new BigInteger("0");
                    buf =  rs.getBigDecimal("PARENT_ID");
                    if(buf != null)
                    {
                        parent_id = buf.toBigInteger();
                    }
                    BigInteger id = rs.getBigDecimal("OBJECT_ID").toBigInteger();
                    do {
                        if (rs.getInt("ATTR_TYPE") == 0){
                            params.put(rs.getBigDecimal("ATTRIBUTE_ID").toBigInteger(), rs.getString("VALUE"));
                        }
                        if (rs.getInt("ATTR_TYPE") == 1) {
                            params.put(rs.getBigDecimal("ATTRIBUTE_ID").toBigInteger(), new Integer(rs.getString("VALUE")));
                        }
                        if (rs.getInt("ATTR_TYPE") == 2) {
                            params.put(rs.getBigDecimal("ATTRIBUTE_ID").toBigInteger(), rs.getString("VALUE"));
                        }
                        if (rs.getInt("ATTR_TYPE") == 3) {
                            params.put(rs.getBigDecimal("ATTRIBUTE_ID").toBigInteger(), rs.getString("VALUE"));
                        }
                        if (rs.getInt("ATTR_TYPE") == 5) {
                            params.put(rs.getBigDecimal("ATTRIBUTE_ID").toBigInteger(), rs.getString("CLOB_VALUE"));
                        }
                    } while (rs.next());
                    result = new DBObject(id, type, name, reference, parent_id, params);
                    return result;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return null;
            }
        });
    }

    public static DBObject getUserByLogin(final String login){
        ArrayList<Object> params = new ArrayList<>();
        params.add(ATTR_LOGIN);
        params.add(login);

        return DBTools.executeSelect("select o.Object_id" +
                "  from Objects O " +
                "  left JOIN ATT_OBJ_TYPES aot on  O.OBJECT_TYPE_ID = aot.OBJECT_TYPE_ID" +
                "  LEFT join ATTRIBUTES a on aot.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join  Params p on p.OBJECT_ID = O.OBJECT_ID and p.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join REFERENCES r on r.OBJECT_ID = O.OBJECT_ID and r.ATTRIBUTE_ID = aot.ATTRIBUTE_ID" +
                "  where a.ATTRIBUTE_ID = ? and p.VALUE = ? ", params, new ResultSetHandler<DBObject>() {
             @Override
             public DBObject handle(ResultSet rs) {
                 try {
                     if (rs.next()){
                        BigInteger id = new BigInteger(rs.getString("OBJECT_ID"));
                        DBObject res = getObject(id);
                        log.error(res.getID());
                        return res;
                     }
                     else return null;
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
                 log.info("Can`t find user");
                 return null;
             }
         });
    }

    public static DBObject getAccountByNumber(final String number){
        ArrayList<Object> params = new ArrayList<>();
        params.add(ATTR_ACCOUNT_NUMBER);
        params.add(number);

        return DBTools.executeSelect("select o.Object_id" +
                "  from Objects O " +
                "  left JOIN ATT_OBJ_TYPES aot on  O.OBJECT_TYPE_ID = aot.OBJECT_TYPE_ID" +
                "  LEFT join ATTRIBUTES a on aot.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join  Params p on p.OBJECT_ID = O.OBJECT_ID and p.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join REFERENCES r on r.OBJECT_ID = O.OBJECT_ID and r.ATTRIBUTE_ID = aot.ATTRIBUTE_ID" +
                "  where a.ATTRIBUTE_ID = ? and p.VALUE = ? ", params, new ResultSetHandler<DBObject>() {
            @Override
            public DBObject handle(ResultSet rs) {
                try {
                    rs.next();
                    BigInteger id = new BigInteger(rs.getString("OBJECT_ID"));
                    DBObject res = getObject(id);
                    log.error(res.getID());

                    return res;
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                log.info("Can`t find user");
                return null;
            }
        });
    }

    public static int updateParam(BigInteger objectID, BigInteger attributeId, Object newValue){
        String query = "UPDATE PARAMS set " +
                    "VALUE = ?, " +
                    "DATA_VALUE = ?," +
                    "CLOB_VALUE = ?" +
                "where ATTRIBUTE_ID = ? " +
                "and OBJECT_ID = ?";
        ArrayList<Object> params = new ArrayList<>();
        if (newValue.getClass().getName().equals("java.lang.String") ||
                newValue.getClass().getName().equals("int") ||
                newValue.getClass().getName().equals("java.lang.Double") ||
                newValue.getClass().getName().equals("Float") ||
                newValue.getClass().getName().equals("java.lang.Integer") ||
                newValue.getClass().getName().equals("java.math.BigInteger")) {
            params.add(newValue);
            params.add(null);
            params.add(null);
        } else if (newValue.getClass().getName().equals("Bank.Transaction$transactionStatus")){
            params.add(newValue.toString());
            params.add(null);
            params.add(null);
        } else if (newValue.getClass().getName().equals("Date")){
            params.add(null);
            params.add(newValue);
            params.add(null);
        } else if (newValue.getClass().getName().equals("Clob")){
            params.add(null);
            params.add(null);
            params.add(newValue);
        } else {
            log.info("Wrong parameter: " + newValue.getClass().getName());
            return 0;
        }
        params.add(attributeId);
        params.add(objectID);

        return DBTools.executeOtherDMLQuery(query, params);
    }
    
    public static BigInteger insertObject(DBObject object){

        BigInteger id = object.getID();

        log.info("start insert object with id = " + id);

        String query = "merge INTO objects o USING " +
                "(SELECT ? AS object_id, " +
                "  ?       AS name, " +
                "  ?       AS reference, " +
                "  ?       AS parent_id, " +
                "  ?       AS object_type_id " +
                "FROM dual " +
                ") curr_obj ON ( o.object_id = curr_obj.object_id ) " +
                "WHEN matched THEN " +
                "  UPDATE " +
                "  SET o.name         = curr_obj.name, " +
                "    o.reference    = curr_obj.reference, " +
                "    o.parent_id      = curr_obj.parent_id, " +
                "    o.object_type_id = curr_obj.object_type_id " +
                "  WHERE o.object_id  = curr_obj.object_id WHEN NOT matched THEN " +
                "  INSERT " +
                "    ( " +
                "      object_id, " +
                "      name, " +
                "      reference, " +
                "      parent_id, " +
                "      object_type_id " +
                "    ) " +
                "    VALUES " +
                "    ( " +
                "      curr_obj.object_id, " +
                "      curr_obj.name, " +
                "      curr_obj.reference, " +
                "      curr_obj.parent_id, " +
                "      curr_obj.object_type_id " +
                "    )" ;

        ArrayList<Object> params = new ArrayList<>();
        params.add(id);
        params.add(object.getName());
        params.add(object.getReference());
        params.add(object.getParent_id());
        params.add(object.getType());
        log.info(DBTools.executeOtherDMLQuery(query, params));

        log.info("object inserted");

        query = "merge INTO params p USING " +
                "(SELECT ? AS object_id, " +
                "  ?       AS ATTRIBUTE_ID, " +
                "  ?       AS value, " +
                "  ?       AS LIST_VAL_ID, " +
                "  ?       AS clob_value, " +
                "  ?       AS data_value " +
                "FROM dual " +
                ") curr_param ON ( p.ATTRIBUTE_ID = curr_param.ATTRIBUTE_ID AND p.object_id = curr_param.object_id ) " +
                "WHEN matched THEN " +
                "  UPDATE " +
                "  SET p.value       = curr_param.value, " +
                "    p.LIST_VAL_ID = curr_param.LIST_VAL_ID, " +
                "    p.data_value    = curr_param.data_value, " +
                "    p.clob_value    = curr_param.clob_value " +
                "  WHERE p.ATTRIBUTE_ID   = curr_param.ATTRIBUTE_ID " +
                "  AND p.object_id   = curr_param.object_id WHEN NOT matched THEN " +
                "  INSERT " +
                "    ( " +
                "      ATTRIBUTE_ID, " +
                "      object_id, " +
                "      value, " +
                "      data_value, " +
                "      LIST_VAL_ID, " +
                "      clob_value " +
                "    ) " +
                "    VALUES " +
                "    ( " +
                "      curr_param.ATTRIBUTE_ID, " +
                "      curr_param.object_id, " +
                "      curr_param.value, " +
                "      curr_param.data_value, " +
                "      curr_param.LIST_VAL_ID, " +
                "      curr_param.clob_value " +
                "    )" ;

        //USER
        if (Objects.equals(object.getType(), USER)){
            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_USER_NAME);
            params.add(object.getAttributeById(ATTR_USER_NAME));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_PHONE);
            params.add(object.getAttributeById(ATTR_PHONE));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_MAIL);
            params.add(object.getAttributeById(ATTR_MAIL));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_LOGIN);
            params.add(object.getAttributeById(ATTR_LOGIN));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_PASSWORD);
            params.add(object.getAttributeById(ATTR_PASSWORD));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);
        }
        //TRANSACTION
        if (Objects.equals(object.getType(), TRANSACTION)){
            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_FROM);
            params.add(object.getAttributeById(ATTR_FROM));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_TARGET);
            params.add(object.getAttributeById(ATTR_TARGET));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_INITIATOR);
            params.add(object.getAttributeById(ATTR_INITIATOR));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_DATE);
            params.add(object.getAttributeById(ATTR_DATE));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_COUNT);
            params.add(object.getAttributeById(ATTR_COUNT));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_TRANSACTION_STATUS);
            params.add(object.getAttributeById(ATTR_TRANSACTION_STATUS));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

        }
        //ACCOUNT
        if (Objects.equals(object.getType(), ACCOUNT)){
            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_AMOUNT);
            params.add(object.getAttributeById(ATTR_AMOUNT));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_USER_ID);
            params.add(object.getAttributeById(ATTR_USER_ID));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            params = new ArrayList<>();
            params.add(id);
            params.add(ATTR_ACCOUNT_NUMBER);
            params.add(object.getAttributeById(ATTR_ACCOUNT_NUMBER));
            params.add(null);
            params.add(null);
            params.add(null);
            DBTools.executeOtherDMLQuery(query, params);

            log.info("Attributes inserted");

            insertReference(id, ATTR_USER_ID, new BigInteger(object.getAttributeById(ATTR_USER_ID).toString()));

        }

        log.info("object with id = " + id + " inserted successful");

        return id;
    }

    public static Transaction getTransaction(DBObject obj){
        if (!Objects.equals(obj.getType(), TRANSACTION)){
            log.debug("Invalid object type");
            return null;
        }

        DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss" , Locale.ENGLISH);
        try {
            return new Transaction(obj.getID(),
                    getAccount(getObject(new BigInteger(obj.getAttributeById(ATTR_FROM).toString()))),
                    getAccount(getObject(new BigInteger(obj.getAttributeById(ATTR_TARGET).toString()))),
                    getUser(getObject(new BigInteger(obj.getAttributeById(ATTR_INITIATOR).toString()))),
                    new Integer(obj.getAttributeById(ATTR_COUNT).toString()),
                    format.parse(obj.getAttributeById(ATTR_DATE).toString()),
                    getStatus(obj.getAttributeById(ATTR_TRANSACTION_STATUS).toString())
            );
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    private static Transaction.transactionStatus getStatus(String val){
        switch (val){
            case "waiting" : return Transaction.transactionStatus.waiting;
            case "inProgress" : return Transaction.transactionStatus.inProgress;
            case "success" : return Transaction.transactionStatus.success;
            case "fail" : return Transaction.transactionStatus.fail;
            case "notEnoughMoney" : return Transaction.transactionStatus.notEnoughMoney;
            default: return Transaction.transactionStatus.fail;
        }
    }

    public static User getUser(DBObject obj){
        if (!Objects.equals(obj.getType(), USER)){
            log.debug("Invalid object type");
            return null;
        }
        return new User(obj.getID(),
                getAccountsByUserID(obj.getID()),
                obj.getAttributeById(ATTR_USER_NAME).toString(),
                obj.getAttributeById(ATTR_MAIL).toString(),
                obj.getAttributeById(ATTR_PHONE).toString(),
                obj.getAttributeById(ATTR_PASSWORD).toString(),
                obj.getAttributeById(ATTR_LOGIN).toString()

        );
    }

    public static Account getAccount(DBObject obj){
        if (!Objects.equals(obj.getType(), ACCOUNT)){
            log.info("Invalid object type");
            return null;
        }

        return new Account(new BigInteger(obj.getAttributeById(ATTR_USER_ID).toString()),
                (int)obj.getAttributeById(ATTR_AMOUNT),
                (int) obj.getAttributeById(ATTR_ACCOUNT_NUMBER),
                obj.getID());

    }

    public static ArrayList<Account> getAccountsByUserID(BigInteger userID){
        ArrayList<Object> params = new ArrayList<>();
        params.add(ACCOUNT);
        params.add(userID);
        params.add(ATTR_USER_ID);
        ArrayList<BigInteger> accounts = DBTools.executeSelect("select o.Object_id, o.Object_type_id, a.ATTRIBUTE_ID, r.REFERENCE" +
                "  from Objects O " +
                "  left JOIN ATT_OBJ_TYPES aot on  O.OBJECT_TYPE_ID = aot.OBJECT_TYPE_ID" +
                "  LEFT join ATTRIBUTES a on aot.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join  Params p on p.OBJECT_ID = O.OBJECT_ID and p.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join REFERENCES r on r.OBJECT_ID = O.OBJECT_ID and r.ATTRIBUTE_ID = aot.ATTRIBUTE_ID" +
                "  where o.Object_type_id = ?  and p.VALUE = ? and p.ATTRIBUTE_ID = ?", params, new ResultSetHandler<ArrayList<BigInteger>>() {
            @Override
            public ArrayList<BigInteger> handle(ResultSet rs) {
                ArrayList<BigInteger> res = new ArrayList<>();
                try {
                    while (rs.next()) {
                        res.add(rs.getBigDecimal("Object_id").toBigInteger());
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return res;
            }
        });
        ArrayList<Account> result = new ArrayList<>();
        for(BigInteger acc: accounts){
            result.add(DBAPI.getAccount(DBAPI.getObject(acc)));
        }
        return result;
    }

    public static ArrayList<Transaction> getTransactionByAccount(BigInteger accID){
        ArrayList<Object> params = new ArrayList<>();
        params.add(TRANSACTION);
        params.add(ATTR_FROM);
        params.add(ATTR_TARGET);
        params.add(accID.toString());
        ArrayList<BigInteger> transactions = DBTools.executeSelect("select o.Object_id, o.Object_type_id, a.ATTRIBUTE_ID, r.REFERENCE" +
                "  from Objects O " +
                "  left JOIN ATT_OBJ_TYPES aot on  O.OBJECT_TYPE_ID = aot.OBJECT_TYPE_ID" +
                "  LEFT join ATTRIBUTES a on aot.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join  Params p on p.OBJECT_ID = O.OBJECT_ID and p.ATTRIBUTE_ID = a.ATTRIBUTE_ID" +
                "  left join REFERENCES r on r.OBJECT_ID = O.OBJECT_ID and r.ATTRIBUTE_ID = aot.ATTRIBUTE_ID" +
                "  where o.Object_type_id = ? and (a.ATTRIBUTE_ID = ? or a.ATTRIBUTE_ID = ?) and p.VALUE = ?", params, new ResultSetHandler<ArrayList<BigInteger>>() {
            @Override
            public ArrayList<BigInteger> handle(ResultSet rs) {
                ArrayList<BigInteger> res = new ArrayList<>();
                try {
                     while (rs.next()) {
                        res.add(rs.getBigDecimal("Object_id").toBigInteger());
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return res;
            }
        });

        ArrayList<Transaction> result = new ArrayList<>();
        for(BigInteger tr: transactions){
            result.add(DBAPI.getTransaction(DBAPI.getObject(tr)));
        }
        return result;

    }

    private static void insertReference(BigInteger object_id, BigInteger attr_id, BigInteger reference){
        String query = "merge INTO references r USING " +
        "(SELECT   ?       AS object_id, " +
                "  ?       AS attribute_id, " +
                "  ?       AS reference " +
                "FROM dual " +
                ") curr_obj ON ( r.attribute_id = curr_obj.attribute_id ) " +
                "WHEN matched THEN " +
                "  UPDATE " +
                "  SET " +
                "    r.object_id    = curr_obj.object_id, " +
                "    r.reference       = curr_obj.reference " +
                "  WHERE r.object_id  = curr_obj.object_id WHEN NOT matched THEN " +
                "  INSERT " +
                "    ( " +
                "      object_id, " +
                "      attribute_id, " +
                "      reference " +
                "    ) " +
                "    VALUES " +
                "    ( " +
                "      curr_obj.object_id, " +
                "      curr_obj.attribute_id, " +
                "      curr_obj.reference " +
                "    )" ;
        ArrayList<Object> params = new ArrayList<>();
        params.add(object_id);
        params.add(attr_id);
        params.add(reference);
        log.info(DBTools.executeOtherDMLQuery(query, params));

    }

}